package com.bobo.dao.favourite;

import com.bobo.dao.BaseDao;
import com.bobo.dto.Counts;
import com.bobo.dto.Page;
import com.bobo.model.*;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by bobo on 16-10-28.
 */
public class FavouriteDaoImpl extends BaseDao implements IFavouriteDao{


    @Override
    public Integer create(Favourite favourite) {
        String sql = "INSERT INTO t_favourite(user_id, topic_id) VALUES (?,?)";
        return create(sql,favourite.getUserId(),favourite.getTopicId());
    }

    @Override
    public boolean delete(Favourite favourite) {
        String sql = "DELETE FROM t_favourite WHERE user_id=? AND topic_id=?";
        return update(sql,favourite.getUserId(),favourite.getTopicId());
    }

    @Override
    public List<Topic> getTopics(Integer userId,Integer current, Integer pageSize) {
        String sql = "SELECT vt.id,vt.category,vt.create_time,vt.hits,vt.img,vt.username,vt.title,vt.favourites,vt.messages\n" +
                "FROM t_favourite tf\n" +
                "left join v_topic_count vt on vt.id=tf.topic_id\n" +
                "WHERE tf.user_id=?";
        List<Topic> topics = new ArrayList<>();
        ResultSet rs = pager(sql, current, pageSize, userId);
        try {
            while (rs.next()){
                Topic topic = new Topic();
                topic.setId(rs.getInt("id"));
                topic.setTitle(rs.getString("title"));
                topic.setHits(rs.getInt("hits"));
                topic.setCreateTime(rs.getTimestamp("create_time"));
                topic.setFavourites(rs.getInt("favourites"));
                topic.setMessages(rs.getInt("messages"));

                Photo p = new Photo();
                p.setImg(rs.getString("img"));
                topic.setPhoto(p);

                User user = new User();
                user.setName(rs.getString("username"));
                topic.setUser(user);

                Category category = new Category();
                category.setName(rs.getString("category"));
                topic.setCategory(category);

                topics.add(topic);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return topics;
    }

    @Override
    public Integer getCount(Integer topicId,Integer userId) {
        StringBuilder sql = new StringBuilder("SELECT count(*) FROM t_favourite ");
        Integer pageNum = 0;
        ResultSet rs;
        if(topicId!=null){
            sql.append("WHERE topic_id=?");
            rs = getObject(sql.toString(),topicId);
        }else {
            sql.append("WHERE user_id=?");
            rs = getObject(sql.toString(), userId);
        }
        try {
            while (rs.next()) {
                pageNum = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pageNum;
    }

    @Override
    public boolean query(Favourite favourite) {
        String sql = "SELECT count(id) FROM t_favourite WHERE topic_id=? AND user_id=?";
        ResultSet rs = getObject(sql, favourite.getTopicId(), favourite.getUserId());
        int count=0;
        try {
            while (rs.next()){
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count>0;
    }

    @Override
    public Counts getCounts(Integer userId) {
        String sql = "select (select  count(tf.topic_id) AS favourites from t_favourite tf WHERE user_id=?) as favourites ,\n" +
                "  (select count(tt.id) from t_topic tt WHERE user_id=?) as topics";
        ResultSet rs = getObject(sql, userId, userId);
        Counts counts = new Counts();
        try {
            while (rs.next()){
                counts.setFavourites(rs.getInt("favourites"));
                counts.setTopics(rs.getInt("topics"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return counts;
    }
}
